package com.icss.th.db.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.icss.th.common.Constants;
import com.icss.th.common.util.DBUtil;
import com.icss.th.common.util.StringUtil;
import com.icss.th.db.pojo.Column;

public class DataExchange 
{
	/**
	 * 从sqlserver库抽取全量数据到mysql
	 */
	public static void fetchData()
	{
		try 
		{
			Connection connSrc = DBUtil.getConn(Constants.DB_TYPE.SQLSERVER);
			Connection connDist = DBUtil.getConn(Constants.DB_TYPE.MYSQL);
			
			try
			{
				System.out.println("start------------subjects");
				translateTable("subjects", connSrc, connDist);
				System.out.println("end------------subjects");
				
				System.out.println("start------------paperInfo");
				translateTable("paperInfo", connSrc, connDist);
				System.out.println("end------------paperInfo");
				
				System.out.println("start------------examData");
				translateTable("examData", connSrc, connDist);
				System.out.println("end------------examData");
			}
			finally
			{
				connSrc.close();
				connDist.close();
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}

	/**
	 * 将一个表的数据从一个数据源转到另一个数据源
	 * @param tableName
	 * @param connSrc
	 * @param connDist
	 * @throws SQLException 
	 */
	private static void translateTable(String tableName, Connection connSrc, Connection connDist) throws SQLException
	{
		Table table = Table.getInstance(tableName, connSrc);
		String insertSql = table.getInsertSql();
		
		Statement st = connSrc.createStatement();
		ResultSet rs = st.executeQuery(table.getQuerySql());
		
		boolean isAutoCommit = connDist.getAutoCommit();
		connDist.setAutoCommit(false);
		PreparedStatement ps = connDist.prepareStatement(insertSql);

		int num = 1;
		while (rs.next())
		{
			for (int i = 0; i < table.getColumns().size(); i++)
			{
				Column c = table.getColumns().get(i);
				c.setValue(ps, c.getValue(rs), i + 1);
			}

			ps.execute();
			
			if ((num++ % 1000) == 0)
			{
				connDist.commit();
				System.out.println("插入第" + (num - 1) + "条");
			}
		}
		
		connDist.commit();
		connDist.setAutoCommit(isAutoCommit);
		
		ps.close();
		rs.close();
		st.close();
	}
	
	/**
	 * 生成试题短标题
	 */
	public static void buildShortTitle() 
	{
		Connection conn = DBUtil.getConn();
		
		try 
		{
			try
			{
				conn.setAutoCommit(false);
				
				PreparedStatement ps = conn.prepareStatement("update ExamData set shortExamTitle=? where id=?");
				
				int num = 1;
				while (num <= 2062)
				{
					Statement st = conn.createStatement();
					ResultSet rs = st.executeQuery("select id,examTitle from ExamData where id>" + (num - 1)*1000 + " and id<=" + num*1000);
					while (rs.next())
					{
						ps.setInt(2, rs.getInt(1));
						ps.setString(1, StringUtil.delHTMLTag(rs.getString(2)));
						ps.execute();
					}
					
					conn.commit();
					rs.close();
					st.close();
					System.out.println(num*1000 + "条插入完毕");
					
					num++;
				}
			}
			finally
			{
				conn.close();
			}
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}
